﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.ReportService.Term
{
    public class GetStatusOfTermListDbCommand : ICommand<IList<TermStatusDTO>>
    {
        private IList<int> _terms;
        private readonly int _userId;
        private const string _Sql = @"  select TermId, MAX(ExpiredDate) as ExpiredDate 
                                        from dbo.Portfolio 
                                        where UserId = @UserId AND TermId IN ({0}) 
                                        group by TermId
                    ";
        public GetStatusOfTermListDbCommand(IList<int> terms, int userId)
        {
            _terms = terms;
            _userId = userId;
        }

        public IList<TermStatusDTO> Execute()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var dt = new DataTable();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(BuildQuery(), con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = _userId;
                    con.Open();
                    adapt.Fill(dt);
                    cmd.ExecuteNonQuery();
                }
                return GetDtos(dt);
            }
        }

        private string BuildQuery()
        {
            string ids = string.Join(",", _terms.ToArray());
            return string.Format(_Sql, ids);
        }
        private IList<TermStatusDTO> GetDtos(DataTable dtTable)
        {
            var dtos = new List<TermStatusDTO>();
            foreach (DataRow dtRow in dtTable.Rows)
            {
                var statusDto = new TermStatusDTO
                {
                    TermId = (int)dtRow["TermId"],
                };
                var expiredDate = dtRow["ExpiredDate"] != DBNull.Value ? (DateTime) dtRow["ExpiredDate"] : DateTime.MinValue;
                if (expiredDate.Date >= DateTime.Now.Date) statusDto.IsInPortfolio = true;
                statusDto.ExpiredDate = expiredDate;
                dtos.Add(statusDto);
            }
            return dtos;
        }
    }
}
